package day02;

import util.JdbcUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@WebServlet("*.emp")
public class EmpServlet extends HttpServlet {


    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setCharacterEncoding("utf-8");

        String path=request.getServletPath();

        System.out.println(path);

        if("/add.emp".equals(path)){
            add(request,response);
        }


        if("/list.emp".equals(path)){
            list(request,response);
        }

        if("/remove.emp".equals(path)){
            remove(request,response);
        }

        if("/load.emp".equals(path)){
            load(request,response);
        }

        if("/modify.emp".equals(path)){
            modify(request,response);
        }

        
    }

    protected void modify(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        Integer id=Integer.parseInt(request.getParameter("id"));
        String name=request.getParameter("name");
        String salaryStr=request.getParameter("salary");
        Double salary="".equals(salaryStr.trim())
                ?null
                :Double.parseDouble(salaryStr);
        String sex=request.getParameter("sex");
        String edu=request.getParameter("edu");
        String[] hobbies=request.getParameterValues("hobbies");
        String description=request.getParameter("description");


        Connection con=null;
        PreparedStatement ps=null;

        try{

            con=JdbcUtil.getConnection();

            con.setAutoCommit(false);


            String sql=new StringBuffer()
                    .append("update t_emp ")
                    .append("set name=?, ")
                    .append("    salary=?, ")
                    .append("    sex=?, ")
                    .append("    edu=?, ")
                    .append("    hobbies=?, ")
                    .append("    description=? ")
                    .append("where id=? ")
                    .toString();


            ps=con.prepareStatement(sql);

            ps.setString(1,name);
            ps.setDouble(2,salary);
            ps.setString(3,sex);
            ps.setString(4,edu);
            ps.setString(5,String.join(",",hobbies));
            ps.setString(6,description);
            ps.setInt(7,id);

            ps.executeUpdate();


            con.commit();

        }catch (Exception e){
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            JdbcUtil.close(con,ps,null);
        }

        response.sendRedirect(request.getContextPath()+"/list.emp");
    }



    protected void load(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        Integer id=Integer.parseInt(request.getParameter("id"));


        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        Emp emp=null;

        try{
            con=JdbcUtil.getConnection();

            con.setAutoCommit(false);


            String sql=new StringBuffer()
                    .append("select * ")
                    .append("from t_emp ")
                    .append("where id=? ")
                    .toString();

            ps=con.prepareStatement(sql);

            ps.setInt(1,id);

            rs=ps.executeQuery();

            if(rs.next()){
                emp=new Emp();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setSalary(rs.getDouble("salary"));
                emp.setSex(rs.getString("sex"));
                emp.setEdu(rs.getString("edu"));
                emp.setHob(rs.getString("hobbies"));
                emp.setDescription(rs.getString("description"));
            }


            con.commit();
        }catch (Exception e){
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            JdbcUtil.close(con,ps,rs);
        }

        request.setAttribute("emp",emp);

        request.getRequestDispatcher("/emp/modify.jsp").forward(request,response);
    }


    protected void remove(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        Integer id=Integer.parseInt(request.getParameter("id"));

        Connection con=null;
        PreparedStatement ps=null;

        try{
            con=JdbcUtil.getConnection();

            con.setAutoCommit(false);


            String sql=new StringBuffer()
                    .append("delete from t_emp ")
                    .append("where id=? ")
                    .toString();

            ps=con.prepareStatement(sql);

            ps.setInt(1,id);

            ps.executeUpdate();


            con.commit();;

        }catch (Exception e){
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            JdbcUtil.close(con,ps,null);
        }

        response.sendRedirect(request.getContextPath()+"/list.emp");
    }


    protected void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        List<Emp> emps=new ArrayList<>();
        try{
            con=JdbcUtil.getConnection();

            con.setAutoCommit(false);


            String sql=new StringBuffer()
                    .append("select * ")
                    .append("from t_emp ")
                    .toString();

            ps=con.prepareStatement(sql);

            rs=ps.executeQuery();


            while(rs.next()){
                Emp emp=new Emp();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setSalary(rs.getDouble("salary"));
                emp.setSex(rs.getString("sex"));
                emp.setEdu(rs.getString("edu"));
                emp.setHob(rs.getString("hobbies"));
                emp.setDescription(rs.getString("description"));

                emps.add(emp);
            }

            con.commit();
        }catch (Exception e){
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            JdbcUtil.close(con,ps,rs);
        }

        request.setAttribute("emps",emps);

        request.getRequestDispatcher("/emp/list.jsp").forward(request,response);
    }



    protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String name=request.getParameter("name");
        String salaryStr=request.getParameter("salary");
        Double salary="".equals(salaryStr.trim())
                ?null
                :Double.parseDouble(salaryStr);
        String sex=request.getParameter("sex");
        String edu=request.getParameter("edu");
        String[] hobbies=request.getParameterValues("hobbies");
        String description=request.getParameter("description");

        Connection con=null;
        PreparedStatement ps=null;
        try{
            con=JdbcUtil.getConnection();
            con.setAutoCommit(false);




            String sql=new StringBuffer()
                    .append("insert into t_emp ")
                    .append("    (name,salary,sex,edu,hobbies,description) ")
                    .append("values ")
                    .append("    (?,?,?,?,?,?) ")
                    .toString();

            ps=con.prepareStatement(sql);
            ps.setString(1,name);
            ps.setDouble(2,salary);
            ps.setString(3,sex);
            ps.setString(4,edu);
            ps.setString(5,String.join(",",hobbies));
            ps.setString(6,description);


            ps.executeUpdate();



            con.commit();


            response.sendRedirect(request.getContextPath()+"/list.emp");

        }catch (Exception e){
            e.printStackTrace();
            try {
                con.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally{
            JdbcUtil.close(con,ps,null);
        }
    }


}
